﻿CREATE PROCEDURE [dbo].[CalcGoodsDefekturaCategories]
(
	@PeriodEnd smalldatetime,
	@FirstCatCoef int = 30,	--отношение дней продаж к дням присутствия в процентах
	@ThirdCatCoef int = 15, --отношение дней продаж к дням присутствия в процентах
	@PresenceCoef int = 40, --коеф. присутствия. Если <= 40 % то ставиться 4-я категория
	@WithAggregate bit = 0,	--=1- расчет с агрегированием товаров у которых одинаковые AssortmentId, RetailPack (точнее DefekturaAggregate = 1)
	@ForthCatCount int OUTPUT
)
AS
/* Черноуосов ОП 16.07.06 Распределение товаров по категориям */
DECLARE @intRet integer,  @intErrCode int, @strAction varchar(50)
DECLARE @PeriodBegin smalldatetime, @realFirstCatCoef real, @realThirdCatCoef real

DECLARE @tblGoods3 TABLE (GoodsId int NOT NULL, SaleDays int NULL, FullDays int NULL, CategoryId int NULL, PresenceCoef real 
PRIMARY KEY (	GoodsId) )

DECLARE @tblGoods1 TABLE (GoodsId int NOT NULL, SaleDays int NULL, FullDays int NULL, CategoryId int NULL, PresenceCoef real 
PRIMARY KEY (	GoodsId) )

DECLARE @tblAggregates TABLE (
	[GoodsId] [int] NOT NULL ,
	AggrGoodsId [int] NOT NULL
	PRIMARY KEY (GoodsId)
) 

	SET NOCOUNT ON

IF (@PeriodEnd Is Null)
BEGIN
	SET @intErrCode = 1
	RAISERROR ('Неправильные параметры в вызове процедуры CalcGoodsCategories!', 11, @intErrCode)
	RETURN @intErrCode
END

SELECT @realFirstCatCoef = cast(@FirstCatCoef as real) / 100.0, @realThirdCatCoef = cast(@ThirdCatCoef as real) / 100.0

--удаляем товары которых не в таблице CA.Goods
DELETE D FROM [PR].[DefekturaGoods] D LEFT OUTER JOIN CA.Goods S
ON D.GoodsId = S.Id WHERE S.Id Is Null
--устанавливаем категорию по умолчанию и сбрасываем данные расчитанные раннее
UPDATE [PR].[DefekturaGoods] SET DefekturaCategoryId = 5,
SaleDays = null, FullDays = null, PresenceCoef = null

--добавляем новые товары
INSERT INTO [PR].[DefekturaGoods] (GoodsId, DefekturaCategoryId)
SELECT S.Id, 5 FROM CA.Goods S LEFT OUTER JOIN [PR].[DefekturaGoods] D 
ON S.Id = D.GoodsId 
WHERE D.GoodsId Is Null
ORDER BY S.Id

--определение 3-й категории
SET @PeriodBegin = dateadd(day, -60, @PeriodEnd )

INSERT INTO @tblGoods3 (GoodsId, FullDays, SaleDays)
SELECT GoodsId, sum(FullDays), 
count(CASE WHEN SaleQntty = 0 THEN NULL ELSE 1 END) 
FROM dbo.NotEmptyDays(NULL, @PeriodBegin, @PeriodEnd, @WithAggregate)
WHERE DocDate >= @PeriodBegin
GROUP BY GoodsId
HAVING sum(FullDays) > 0

--Если порог присутствия меньше параметра то товар перемещяем в 4-ю категорию
UPDATE @tblGoods3 SET 
PresenceCoef = ( 1.0 * FullDays ) / 60.0,
CategoryId = CASE WHEN ( 1.0 * FullDays ) / 60.0 < (1.0 * @PresenceCoef) / 100.0 THEN 4 ELSE NULL END

--Для оставшихся товаров определяем возможность перемещения в 3-ю каегорию
UPDATE @tblGoods3 SET 
CategoryId = CASE WHEN (1.0 * SaleDays )/ ( 1.0 * FullDays ) < @realThirdCatCoef THEN 3 ELSE NULL END
WHERE CategoryId Is Null

--определение 1-й категории
SET @PeriodBegin = dateadd(day, -30, @PeriodEnd)
INSERT INTO @tblGoods1 (GoodsId, FullDays, SaleDays)
SELECT GoodsId, sum(FullDays), 
count(CASE WHEN SaleQntty = 0 THEN NULL ELSE 1 END) 
FROM dbo.NotEmptyDays(NULL, @PeriodBegin, @PeriodEnd, @WithAggregate)
WHERE DocDate >= @PeriodBegin
GROUP BY GoodsId
HAVING sum(FullDays) > 0

--Если порог присутствия меньше параметра то товар перемещяем в 4-ю категорию
UPDATE @tblGoods1 SET 
PresenceCoef = ( 1.0 * FullDays ) / 30.0,
CategoryId = CASE WHEN ( ( 1.0 * FullDays ) / 30.0 ) < ( (1.0 * @PresenceCoef) / 100.0 ) THEN 4 ELSE NULL END

--Для оставшихся товаров определяем возможность перемещения в 1-ю каегорию
UPDATE @tblGoods1 SET 
CategoryId = CASE WHEN (1.0 * SaleDays )/ ( 1.0 * FullDays ) >= @realFirstCatCoef THEN 1 ELSE NULL END
WHERE CategoryId Is Null

--Отмечаем товары попавшие в 1-ю по результатам расчета 1-й категории
UPDATE T3 SET T3.CategoryId = T1.CategoryId, T3.PresenceCoef = T1.PresenceCoef
FROM @tblGoods3 T3 INNER JOIN @tblGoods1 T1 ON T3.GoodsId = T1.GoodsId AND (T1.CategoryId Is Not Null)

--во 2-ю категорию попадают все товары которые не попали в 1-ю или 3-ю категории
UPDATE @tblGoods3 SET CategoryId = 2
WHERE CategoryId Is NULL --PresenceCoef < (1.0 * @PresenceCoef) / 100.0 
AND (1.0 * SaleDays )/ ( 1.0 * FullDays ) >= @realThirdCatCoef
AND (1.0 * SaleDays )/ ( 1.0 * FullDays ) < @realFirstCatCoef

--Последние товаров без категории перемещяем в 4-ю категорию (для этих товаров категории нужно выставить вручную)
UPDATE @tblGoods3 SET CategoryId = 4
WHERE CategoryId Is NULL

IF @WithAggregate = 1 
BEGIN
--вычисляем агрегированнные товары 
	INSERT INTO @tblAggregates (GoodsId, AggrGoodsId)
	SELECT G.Id AS GoodsId, A.AggrGoodsId
	FROM ( --изменено 23,11,06
		SELECT T.AssortmentId, T.RetailPack, max(T.Id) as AggrGoodsId --самый новый код товара
		FROM CA.Goods T INNER JOIN (
			SELECT GoodsId FROM CD.Parties --только те товары которые были в обороте
			UNION
			SELECT GoodsId FROM CD.InputLines -- и в приходе
		) D ON T.Id = D.GoodsId
		WHERE T.DefekturaAggregate = 1
		GROUP BY AssortmentId, RetailPack
	) A INNER JOIN CA.Goods G ON A.AssortmentId = G.AssortmentId AND A.RetailPack = G.RetailPack AND G.DefekturaAggregate = 1


	INSERT INTO @tblAggregates (GoodsId, AggrGoodsId)
	SELECT A.GoodsId, A.GoodsId FROM (
		SELECT DISTINCT GoodsId FROM CD.Parties WHERE GoodsId NOT IN (SELECT GoodsId FROM @tblAggregates)
	) As A

	UPDATE G SET G.DefekturaCategoryId = T.CategoryId,
	G.SaleDays = T.SaleDays,
	G.FullDays = T.FullDays,
	G.PresenceCoef = T.PresenceCoef
	FROM [PR].[DefekturaGoods] G INNER JOIN (
		SELECT GoodsId, CategoryId, SaleDays, FullDays, PresenceCoef FROM @tblGoods3 
		UNION
		SELECT A.GoodsId,  --CASE WHEN A.AggrGoodsId IS NULL THEN S.GoodsId ELSE A.AggrGoodsId END AS GoodsId,
		S.CategoryId, S.SaleDays, S.FullDays, S.PresenceCoef
		FROM @tblGoods3 S INNER JOIN @tblAggregates A ON S.GoodsId = A.AggrGoodsId
	) T ON G.GoodsId = T.GoodsId  

END ELSE BEGIN
	UPDATE G SET G.DefekturaCategoryId = T.CategoryId,
	G.SaleDays = T.SaleDays,
	G.FullDays = T.FullDays,
	G.PresenceCoef = T.PresenceCoef
	FROM [PR].[DefekturaGoods] G INNER JOIN @tblGoods3 T ON G.GoodsId = T.GoodsId 
END

SELECT @ForthCatCount = count(*) FROM [PR].[DefekturaGoods] WHERE DefekturaCategoryId = 4

	RETURN 0
